﻿<%@ WebHandler Language="C#" Class="WorkflowBaseFileUploadHandler" %>

using System;
using System.Web;
using Newtonsoft.Json;
using System.Linq;
using System.Web.SessionState;
using System.Collections.Generic;
using BusinessRule.DTO;
using System.Data;
using System.IO;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Collections;
using System.Web.SessionState;
using BusinessRule.Business;
using Camstar.WebClient.WebServicesProxy.InSiteWebServices;
using DatabaseAccess.DBUtility;
using System.Text.RegularExpressions;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
using System.Net;

//Description: 工艺上传数据处理程序
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:zhangrj
//create Date:2025-06-17
public class WorkflowBaseFileUploadHandler : IHttpHandler, IRequiresSessionState
{
    uLMSContainerIBusiness m_ContainerObj = uMESBusinessFactory.instance.GetuLMSContainerObject();
    BaseDataUploadBusiness bduBusiness = new BaseDataUploadBusiness();
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    CurrentUserInfo m_UserInfo = new CurrentUserInfo();
    string fileBasePath = "/InSiteWebApplication/Temp2/WFBaseData/"; //上传临时文件路径
    string upload76Path = "http://localhost/FilesWebService/FilesReceiveWebService.asmx/ReceiveWorkflowFile";//工艺文件上传76地址
    private static object thisLock = new object();
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            if (m_UserInfo == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "登录过期，请重新登录" }));
                return;
            }
            lock (thisLock) //锁定，不能同时处理
            {
                string action = context.Request["action"].ToString();
                switch (action)
                {
                    case "UploadWorkflowExcel"://上传工艺规程文件
                        UploadWorkflowExcel(context);
                        break;
                    case "SaveWorkflowDatas"://保存工艺流程上传数据
                        SaveWorkflowDatas(context);
                        break;
                    case "UploadSpecAttachFiles"://上传工序附件
                        UploadSpecAttachFiles(context);
                        break;
                    default:
                        break;
                }
            }
        }
        catch (Exception ex)
        {
            uMESProcessResult result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    /// <summary>
    /// 保存工序附件信息
    /// </summary>
    /// <param name="context"></param>
    private void UploadSpecAttachFiles(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            if (file.ContentLength == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            string fileName = string.Format("{0}{1}", file.FileName, DateTime.Now.ToString("yyyyMMddHHmmssffff"));//重新生成文件名
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "上传成功", Result = true, ReturnData = new List<string>() { fileName, file.FileName } }));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }


    #region 保存工艺规程
    /// <summary>
    /// 保存上传预览的工艺、工序数据
    /// </summary>
    /// <param name="context"></param>
    public void SaveWorkflowDatas(HttpContext context)
    {
        uMESProcessResult results = new uMESProcessResult();//返回信息对象
        ResultStatus res = new ResultStatus();//数据验证返回对象
        DataTable dtPageWorkflow = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["dtWorkflow"].ToString());//页面上传的工艺数据
        DataTable dtPageSpec = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["dtSpec"].ToString());//页面上传的工序数据
        if (dtPageWorkflow.Rows.Count == 0 || dtPageSpec.Rows.Count == 0)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "缓存过期，请重新导入" }));
            return;
        }
        if (!File.Exists(context.Server.MapPath(string.Format("{0}{1}", fileBasePath, dtPageWorkflow.Rows[0]["FileName"].ToString())))) //如果工艺导入文件找不到，则不允许保存
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "文件不存在，请重新导入" }));
            return;
        }
        DataTable dtStep = new DataTable(); //创建工艺时需要关联的工序列表
        dtStep.Columns.Add("StepName");
        dtStep.Columns.Add("ObjName");
        dtStep.Columns.Add("ObjRev");
        dtStep.Columns.Add("ObjType");
        dtStep.Columns.Add("Description");
        res = SaveWorkflowStep(dtPageWorkflow, dtPageSpec, ref dtStep);//保存工序数据
        if (!res.IsError)
        {
            res = SaveWorkflowInfo(dtPageWorkflow, dtStep);//保存工艺数据
        }
        if (!res.IsError)//保存工艺成功后，处理各种数据
        {
            /*
             * 1、保存工艺相关数据。更新件号工艺关联关系（不用更新到件号上，不然会变为件号的默认工艺）
             */
            DataRow drWorkflow = dtPageWorkflow.Rows[0];
            /*先找到上传的工艺的ID以及对应的件号ID*/
            DataTable dtProductQue = bduBusiness.GetRDOInfo("product", drWorkflow["ProductName"].ToString(), drWorkflow["PartRevID"].ToString());
            DataTable dtWorkflowQue = bduBusiness.GetRDOInfo("workflow", drWorkflow["WorkflowName"].ToString(), drWorkflow["WorkflowRev"].ToString());
            string productID = dtProductQue.Rows[0]["productID"].ToString();
            string workflowID = dtWorkflowQue.Rows[0]["workflowID"].ToString();
            /*通过ID去插入到关联关系表中*/
            string strSql = string.Format("select 1 from ProductWorkflowMapping where ProductID='{0}' and WorkflowID='{1}'", productID, workflowID);
            if (DatabaseHelper.GetSingle(strSql) == null)
            {
                strSql = string.Format(@"insert into ProductWorkflowMapping  (ProductID, WorkflowID) values ('{0}', '{1}')", productID, workflowID);
                DatabaseHelper.ExecuteSql(strSql);
            }
            string filePageName = string.Format("{0}${1}", drWorkflow["WorkflowName"].ToString(), drWorkflow["WorkflowRev"].ToString());//文件夹名
            /*保存工艺的附件*/
            SaveAttachFileInfo(filePageName, drWorkflow["FileName"].ToString(), workflowID);//保存工艺附件
            /*
             * 2、保存工序相关数据信息
             */
            SaveSpecAttrInfo(workflowID, dtPageSpec, filePageName);
        }
        results.Message = res.IsError ? res.Message : "保存成功";
        results.Result = !res.IsError;
        context.Response.Write(JsonConvert.SerializeObject(results));
    }
    /// <summary>
    /// 保存工序属性信息
    /// </summary>
    /// <param name="workflowID"></param>
    /// <param name="dtPageSpec"></param>
    /// <param name="filePageName"></param>
    /// <returns></returns>
    private bool SaveSpecAttrInfo(string workflowID, DataTable dtPageSpec, string filePageName)
    {
        try
        {
            string querySql = string.Format(@" SELECT s.specid,s.specno,s.opdesc,ws.workflowstepid FROM workflowstep ws 
                                 LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                                 LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                                 WHERE ws.workflowid = '{0}'", workflowID);
            DataTable dtSpec = DatabaseHelper.Query_ReturnDatTable(querySql);//查询出工艺下的所有工序
            string isSpecResSql = "select 1 from SpecResourceInfo sri where sri.specid='{0}' and sri.Resourceid='{1}'";//查询工序设备关联关系是否有值
            string insertSpecResSql = "insert into SpecResourceInfo (specresourceinfoid,Resourceid,specid) values (sys_guid(),'{0}','{1}') ";//插入工序设备关联表
            string updateSpecSql = "UPDATE workflowstep ws SET ws.makecycle = '{0}' WHERE ws.workflowstepid = '{1}'";//更新工序自身数据信息
            ArrayList sqlList = new ArrayList();
            /*
             * 循环页面导入的工序
             * 做每个工序的相关数据操作
             */
            foreach (DataRow item in dtPageSpec.Rows)
            {
                DataRow drSpec = dtSpec.Select(string.Format("specno = '{0}' AND opdesc = '{1}'", item["SpecNo"].ToString().Trim(), item["OpDesc"].ToString().Trim())).FirstOrDefault();
                if (drSpec == null) //如果找不到该工序，则跳过（理论上不会找不到）
                {
                    continue;
                }
                /*1、保存工序设备关联*/
                if (!string.IsNullOrWhiteSpace(item["ApsResourceID"].ToString())) //如果设备不为空，则关联工序设备
                {
                    if (DatabaseHelper.GetSingle(string.Format(isSpecResSql, drSpec["specid"].ToString(), item["ApsResourceID"].ToString())) == null)
                    {
                        sqlList.Add(string.Format(insertSpecResSql, item["ApsResourceID"].ToString(), drSpec["specid"].ToString()));
                    }
                }
                /*2、更新工序的排产加工时间*/
                sqlList.Add(string.Format(updateSpecSql, item["ApsMakeTime"].ToString(), drSpec["workflowstepid"].ToString()));
                /*3、保存工序附件*/
                if (!string.IsNullOrWhiteSpace(item["FileName"].ToString()))
                {
                    SaveAttachFileInfo(filePageName, item["FileName"].ToString(), drSpec["specid"].ToString());//保存工序附件
                }
            }
            if (sqlList.Count > 0) //如果执行SQL不为空，则执行
            {
                DatabaseHelper.ExecuteSqlTran(sqlList);
            }
            return true;
        }
        catch
        {
            return false;
        }
    }
    /// <summary>
    /// 保存工序的数据
    /// </summary>
    /// <param name="recordDt"></param>
    /// <returns></returns>
    private ResultStatus SaveWorkflowStep(DataTable dtWorkflow, DataTable dtPageSpec, ref DataTable dtStep)
    {
        ResultStatus result = new ResultStatus();
        Dictionary<string, string> dic = new Dictionary<string, string>();
        string productName = dtWorkflow.Rows[0]["ProductName"].ToString();//件号
        string partRevID = dtWorkflow.Rows[0]["PartRevID"].ToString();//设计图版次
        string workflowFactoryName = dtWorkflow.Rows[0]["WorkflowFactoryName"].ToString();//工艺所属分厂
        DataTable pdt = bduBusiness.GetRDOInfo("product", productName, partRevID);
        if (pdt.Rows.Count == 0)
        {
            result.IsError = true;
            result.Message = string.Format("系统中无【件号：{0}，版次：{1}】的数据，请检查文件数据", productName, partRevID);
            return result;
        }
        //创建工序
        foreach (DataRow item in dtPageSpec.Rows)
        {
            dic.Clear();
            string specNo = item["SpecNo"].ToString();
            string specName = productName + "-" + specNo + "-" + item["OpDesc"].ToString().Trim();
            dic.Add("SpecName", specName);//工序名称
            dic.Add("SpecRev", item["SpecRev"].ToString().Trim());//工序版本
            dic.Add("SpecNO", specNo.Trim());//工序号
            dic.Add("OPDesc", item["OpDesc"].ToString().Trim());//工序名
            dic.Add("Operation", "排除故障");//工种
            dic.Add("Factory", item["SpecFactoryName"].ToString());//分厂
            dic.Add("SpecDesc", item["SpecDesc"].ToString().Replace("\n", "").Replace(" ", ""));//工序内容
            dic.Add("UserName", m_UserInfo.APIUserName.ToString());
            dic.Add("Password", m_UserInfo.APIPassword.ToString());
            dic.Add("SpecOldRev", (int.Parse(item["SpecRev"].ToString()) - 1).ToString());//旧版本
            switch ((SpecType)Enum.Parse(typeof(SpecType), item["SpecType"].ToString())) //根据类型设置不同的属性（后续可根据需求新增类型）
            {
                case SpecType.普通工序:
                    dic.Add("ControlCode", "");//控制码
                    dic.Add("IsLastCheck", "0");//设置不是检验序
                    dic.Add("Iskey", "0");//设置不是关键序
                    break;
                case SpecType.转工工序:
                    dic.Add("ControlCode", "");//控制码
                    dic.Add("IsLastCheck", "0");//设置不是检验序
                    dic.Add("Iskey", "0");//设置不是关键序
                    break;
                case SpecType.检验工序:
                    dic.Add("ControlCode", "");//控制码
                    dic.Add("IsLastCheck", "1");//设置是检验序
                    dic.Add("Iskey", "0");//设置不是关键序
                    break;
                case SpecType.转工检验工序:
                    dic.Add("ControlCode", "");//控制码
                    dic.Add("IsLastCheck", "1");//设置是检验序
                    dic.Add("Iskey", "0");//设置不是关键序
                    break;
                default:
                    dic.Add("ControlCode", "");//控制码
                    dic.Add("IsLastCheck", "0");//设置不是检验序
                    dic.Add("Iskey", "0");//设置不是关键序
                    break;
            }

            DataTable dt = bduBusiness.GetRDOInfo("spec", specName, item["SpecRev"].ToString().Trim());
            if (dt.Rows.Count == 0)//如果不存在该版本工序，则添加
            {
                result = item["SpecRev"].ToString() == "1" ? commonBusiness.CreateSpec(dic) : commonBusiness.CreateNewRevSpec(dic);
                if (result.IsError)
                {
                    break;
                }
            }
            /*将该工序添加到工艺关联中去*/
            DataRow dr = dtStep.NewRow();
            dr["StepName"] = dic["SpecNO"] + "-" + dic["OPDesc"];
            dr["ObjName"] = dic["SpecName"];
            dr["ObjRev"] = dic["SpecRev"];
            dr["ObjType"] = "Spec";
            dr["Description"] = dic["SpecDesc"];
            dtStep.Rows.Add(dr);
        }
        return result;
    }

    /// <summary>
    /// 保存工艺数据
    /// </summary>
    /// <param name="dtWorkflow"></param>
    /// <param name="dtStep"></param>
    /// <returns></returns>
    private ResultStatus SaveWorkflowInfo(DataTable dtWorkflow, DataTable dtStep)
    {
        ResultStatus result = new ResultStatus();
        Dictionary<string, string> dic = new Dictionary<string, string>();
        string productName = dtWorkflow.Rows[0]["ProductName"].ToString();//件号
        string workflowName = dtWorkflow.Rows[0]["WorkflowName"].ToString();//工艺名称
        string workflowRev = dtWorkflow.Rows[0]["WorkflowRev"].ToString();//工艺版本
        string process_No = dtWorkflow.Rows[0]["Process_No"].ToString();//工艺规程编号
        string partRevID = dtWorkflow.Rows[0]["PartRevID"].ToString();//设计图版次
        string workflowFactoryName = dtWorkflow.Rows[0]["WorkflowFactoryName"].ToString();//工艺所属分厂
        dic.Add("WorkflowName", workflowName);//工艺名
        dic.Add("Rev", workflowRev);//工艺版本
        dic.Add("Description", " ");//工艺描述
        dic.Add("ProductName", productName);//产品名
        dic.Add("ProductRev", partRevID);//产品版本
        dic.Add("Factory", workflowFactoryName);//分厂
        dic.Add("PartRevID", partRevID);//设计图版次
        dic.Add("WorkflowType2", "排除故障");//工艺类型
        dic.Add("UserName", m_UserInfo.APIUserName.ToString());
        dic.Add("Password", m_UserInfo.APIPassword.ToString());
        if (workflowRev == "1")//如果版本是1，创建新对象，否则创建新版本
        {
            dic.Add("Process_No", process_No);//工艺规程编号
            dic.Add("Notes", " ");
            dic.Add("WorkFlowEditFlag", "Add");
            result = commonBusiness.SaveWorkflow(dic, dtStep);
        }
        else
        {
            dic.Add("ProcessNo", process_No);//工艺规程编号
            dic.Add("OldRev", (int.Parse(workflowRev) - 1).ToString());//旧版本
            result = commonBusiness.CreateWorkflowRev(dic, dtStep);
        }
        return result;
    }
    #endregion

    #region 上传预览

    /// <summary>
    /// 上传工艺规程文件并预览
    /// </summary>
    /// <param name="context"></param>
    public void UploadWorkflowExcel(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            if (!Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            if (file.ContentLength == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            string IsXls = Path.GetExtension(file.FileName);//获取上传文件的扩展名
            if (IsXls != ".xlsx" && IsXls != "xls") //如果不是Excel表格文件，则不允许上传
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "文件格式不正确！", Result = false }));
                return;
            }
            string fileName = string.Format("{0}{1}{2}", Path.GetFileNameWithoutExtension(file.FileName), DateTime.Now.ToString("yyyyMMddHHmmssffff"), IsXls);//重新生成文件名
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件

            IWorkbook Workbook = GetWorkbookObj(fpath);//获取文件内容对象
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            ISheet sheet = Workbook.GetSheetAt(0);//只取第一个sheet页
            uMESProcessResult result = GetWorkflowExcelData(sheet, IsXls, fileName);//解析文件并获取数据
            if (!result.Result)
            {
                DeleteFile(fpath);
            }
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 获取解析后的工艺规程数据
    /// </summary>
    /// <param name="sheet">sheet页</param>
    /// <param name="xlsType">上传文件的后缀类型</param>
    /// <param name="fileName">上传文件的最终文件名</param>
    /// <returns></returns>
    public uMESProcessResult GetWorkflowExcelData(ISheet sheet, string xlsType, string fileName)
    {
        DataTable dtPageWorkflow = new DataTable() { TableName = "dtPageWorkflow" };//工艺信息
        dtPageWorkflow.Columns.Add("ProductName");
        dtPageWorkflow.Columns.Add("PartRevID");
        dtPageWorkflow.Columns.Add("WorkflowName");
        dtPageWorkflow.Columns.Add("WorkflowRev");
        dtPageWorkflow.Columns.Add("Process_No");
        dtPageWorkflow.Columns.Add("WorkflowFactoryName");
        dtPageWorkflow.Columns.Add("FileName");
        dtPageWorkflow.Rows.Add("", "", "", "", "", "", fileName);//工艺默认值
        DataTable dtPageSpec = new DataTable() { TableName = "dtPageSpec" };//工序信息
        dtPageSpec.Columns.Add("SpecNo");
        dtPageSpec.Columns.Add("OpDesc");
        dtPageSpec.Columns.Add("SpecDesc");
        dtPageSpec.Columns.Add("SpecRev");
        dtPageSpec.Columns.Add("ApsMakeTime");
        dtPageSpec.Columns.Add("ApsResource");
        dtPageSpec.Columns.Add("ApsResourceID");
        dtPageSpec.Columns.Add("SpecFactoryName");
        dtPageSpec.Columns.Add("SpecType");
        dtPageSpec.Columns.Add("FileName");
        dtPageSpec.Columns.Add("RealFileName");
        int i = 1;//行数计数
        bool isValid = true; //是否有效（判断数据有效性）
        string errorMsg = "";//错误信息
        IEnumerator rows = sheet.GetRowEnumerator();
        IRow row = null;
        while (rows.MoveNext()) //循环读取EXCEL的每一行
        {
            switch (xlsType) //循环每一行时，都要重新赋值（不能放在循环外）
            {
                case ".xlsx":
                    row = (XSSFRow)rows.Current;
                    break;
                case ".xls":
                    row = (HSSFRow)rows.Current;
                    break;
            }
            if (row.Cells.Count == 0)//如果单元格为空，结束循环
            {
                break;
            }
            /*---------------------------------------------------------*/
            if (i < 6) //如果6行前，读取工艺信息
            {
                Tuple<bool, string> res = GetTableWorkflowInfo(dtPageWorkflow.Rows[0], i, row);
                if (!res.Item1)
                {
                    isValid = false; errorMsg = res.Item2; break;
                }
            }
            /*---------------------------------------------------------*/
            else //从第六行开始，读取工序及后续信息
            {
                if (GetCellValue(row.GetCell(0)).Equals("编制"))//第一列包含编制，结束循环
                {
                    break;
                }
                string specNo = GetCellValue(row.GetCell(0));//获取第一列数据
                specNo = string.IsNullOrWhiteSpace(specNo) ? "" : specNo.PadLeft(4, '0');
                DataRow dr = dtPageSpec.NewRow();
                dr["SpecNo"] = specNo;//工序号
                dr["OpDesc"] = GetCellValue(row.GetCell(1));//工序名称
                dr["SpecDesc"] = GetCellValue(row.GetCell(2));//工序描述
                dr["ApsMakeTime"] = GetCellValue(row.GetCell(3));//排产加工工时
                dr["ApsResource"] = GetCellValue(row.GetCell(4));//排产设备
                dr["SpecFactoryName"] = GetCellValue(row.GetCell(5));//工序分厂
                dr["SpecType"] = GetCellValue(row.GetCell(6));//工序类型
                Tuple<bool, bool, string> res = IsValidSpecRow(ref dr, i, dtPageWorkflow.Rows[0]);//验证数据
                if (!res.Item1) //如果报错，则提示，并结束上传
                {
                    isValid = false; errorMsg = res.Item3; break;
                }
                if (res.Item1 && res.Item2)//有效的行数据才添加，否则跳过该行
                {
                    string specName = dtPageWorkflow.Rows[0]["ProductName"].ToString() + "-" + specNo + "-" + dr["OpDesc"].ToString();
                    DataTable dtSpec = bduBusiness.GetRDOInfo("spec", specName, "");
                    if (dtSpec.Rows.Count == 0)//没有该工序
                    {
                        dr["SpecRev"] = "1";//工序版本
                    }
                    else
                    {
                        dr["SpecRev"] = (int.Parse(dtSpec.Rows[0]["specrevision"].ToString()) + 1).ToString();
                    }
                    dtPageSpec.Rows.Add(dr);
                }
            }
            i++;
        }
        return new uMESProcessResult() { Message = isValid ? "获取成功" : errorMsg, Result = isValid, DBDataSet = new DataSet() { Tables = { dtPageSpec, dtPageWorkflow } } };
    }

    /// <summary>
    /// 获取表中工艺的信息
    /// </summary>
    /// <param name="dr">储存工艺信息</param>
    /// <param name="i"></param>
    /// <param name="row"></param>
    /// <returns></returns>
    private Tuple<bool, string> GetTableWorkflowInfo(DataRow dr, int i, IRow row)
    {
        Tuple<bool, string> res = new Tuple<bool, string>(true, "");
        switch (i)
        {
            case 2: //第二行
                dr["ProductName"] = GetCellValue(row.GetCell(2));//获取件号（C2）
                if (string.IsNullOrWhiteSpace(dr["ProductName"].ToString()))
                {
                    res = new Tuple<bool, string>(false, "零件号为空，请检查文件数据");
                }
                break;
            case 4: //第四行
                dr["WorkflowFactoryName"] = GetCellValue(row.GetCell(1));//获取分厂（B4）
                if (string.IsNullOrWhiteSpace(dr["WorkflowFactoryName"].ToString()))
                {
                    res = new Tuple<bool, string>(false, "分厂为空，请检查文件数据");
                    break;
                }
                DataTable dtFactory = GetFactoryInfo(dr["WorkflowFactoryName"].ToString());
                if (dtFactory.Rows.Count == 0)
                {
                    res = new Tuple<bool, string>(false, "系统中无该分厂名称，请检查文件数据");
                    break;
                }
                string factoryCode = dtFactory.Rows[0]["factorycode"].ToString();
                if (string.IsNullOrWhiteSpace(factoryCode))
                {
                    res = new Tuple<bool, string>(false, "分厂对应的分厂代码为空或不存在，请检查文件数据");
                    break;
                }
                dr["PartRevID"] = GetCellValue(row.GetCell(3));//获取设计图版次（D4）
                if (string.IsNullOrWhiteSpace(dr["PartRevID"].ToString()))
                {
                    res = new Tuple<bool, string>(false, "设计图版次为空，请检查文件数据");
                    break;
                }
                DataTable dtProduct = bduBusiness.GetRDOInfo("product", dr["ProductName"].ToString(), dr["PartRevID"].ToString());
                if (dtProduct.Rows.Count == 0)//没有该件号版本，则不能导入
                {
                    res = new Tuple<bool, string>(false, string.Format("系统中无【件号：{0}，版次：{1}】的数据，请检查文件数据", dr["ProductName"].ToString(), dr["PartRevID"].ToString()));
                    break;
                }
                dr["WorkflowName"] = GetWorkflowName(factoryCode, dr["ProductName"].ToString());//工艺名称
                dr["Process_No"] = dr["WorkflowName"].ToString().Replace("WF-", "");
                DataTable dtWorkflow = bduBusiness.GetRDOInfo("workflow", dr["WorkflowName"].ToString(), "");
                if (dtWorkflow.Rows.Count == 0)//没有该工艺规程
                {
                    dr["WorkflowRev"] = "1";
                }
                else
                {
                    dr["WorkflowRev"] = (int.Parse(dtWorkflow.Rows[0]["workflowrevision"].ToString()) + 1).ToString();
                }
                break;
            default:
                break;
        }
        return res;
    }

    #endregion

    #region 上传公用方法

    /// <summary>
    /// 保存附件信息
    /// </summary>
    /// <param name="filePageName">文件夹名（由于76上的附件保存都存在工艺命名的文检夹下的，所以需要这个动态的文件夹名）</param>
    /// <param name="fileName">文件名</param>
    /// <param name="parentID">对象ID</param>
    private void SaveAttachFileInfo(string filePageName, string fileName, string parentID)
    {
        try
        {
            string teamPath = string.Format("{0}{1}{2}", AppDomain.CurrentDomain.BaseDirectory, fileBasePath.Replace("/InSiteWebApplication/", ""), fileName);//附件当前地址
            if (File.Exists(teamPath))
            {
                IPHostEntry host = Dns.GetHostEntry(Dns.GetHostName());
                IPAddress ip = host.AddressList.FirstOrDefault(p => p.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork);//获取ipv4地址
                FilesReceiveWebService.FilesReceiveWebService fileWeb = new FilesReceiveWebService.FilesReceiveWebService();
                FilesReceiveWebService.ResModel res = fileWeb.SaveWorkflowDocFiles(ip.ToString(), filePageName, fileName);
                if (res.IsSuccess)
                {
                    DeleteFile(teamPath);//上传成功后删除文件
                    /*并将数据插入到文件关联表中*/
                    InsertObjectDoc(parentID, string.Format("MESUpload/{0}/{1}", filePageName, fileName), fileName);
                }
                else
                {
                    /*如果出错，不删除文件，就存75上的文件路径*/
                    InsertObjectDoc(parentID, string.Format("{0}{1}", fileBasePath.Replace("/InSiteWebApplication/", ""), fileName), fileName);
                }

                ///*将文件上传到76服务器上*/
                //bool isSuccess = true;
                //FileStream filestream = new FileStream(teamPath, FileMode.Open, FileAccess.Read);
                //BinaryReader br = new BinaryReader(filestream);
                //byte[] bts = new byte[1024 * 1024]; //分段传输大小，一次传输1MB。
                //int byteRead;
                //while ((byteRead = br.Read(bts, 0, bts.Length)) > 0) //分段传输
                //{
                //    HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(upload76Path);
                //    req.Method = "post";
                //    req.Headers.Add("fileName", Convert.ToBase64String(Encoding.Unicode.GetBytes(fileName)));
                //    req.Headers.Add("filePageName", Convert.ToBase64String(Encoding.Unicode.GetBytes(filePageName)));
                //    req.ContentType = "multipart/form-data";
                //    req.ContentLength = byteRead;
                //    Stream reqStream = req.GetRequestStream();
                //    reqStream.Write(bts, 0, byteRead);
                //    reqStream.Close();
                //    WebResponse wr = (HttpWebResponse)req.GetResponse();
                //    Stream sr = wr.GetResponseStream();
                //    StreamReader reader = new StreamReader(sr, Encoding.UTF8);
                //    string res = reader.ReadToEnd();
                //    Dictionary<string, string> resDic = JsonConvert.DeserializeObject<Dictionary<string, string>>(res);
                //    if (!bool.Parse(resDic["IsSuccess"])) //如果保存失败，则退出
                //    {
                //        isSuccess = false;
                //        break;
                //    }
                //}
                //filestream.Close();
                //br.Close();
                //if (isSuccess)
                //{
                //    DeleteFile(teamPath);//上传成功后删除文件
                //    /*并将数据插入到文件关联表中*/
                //    InsertObjectDoc(parentID, string.Format("MESUpload/{0}/{1}", filePageName, fileName), fileName);
                //}
                //else
                //{
                //    /*如果出错，不删除文件，就存75上的文件路径*/
                //    InsertObjectDoc(parentID, string.Format("{0}{1}", fileBasePath.Replace("/InSiteWebApplication/", ""), fileName), fileName);
                //}
            }
        }
        catch (Exception e)
        {
            /*
             * 如果出错，就存75上的文件路径
             * 并将数据插入到文件关联表中*/
            InsertObjectDoc(parentID, string.Format("{0}{1}", fileBasePath.Replace("/InSiteWebApplication/", ""), fileName), fileName);
        }
    }

    /// <summary>
    /// 插入到文件关系表中
    /// </summary>
    /// <param name="parentID"></param>
    /// <param name="filePath"></param>
    /// <param name="fileName"></param>
    private void InsertObjectDoc(string parentID, string filePath, string fileName)
    {
        string strSql = @"INSERT INTO OBJECTDOCINFO ob (ob.objectdocinfoid,ob.parentid,ob.filepath,ob.filename)
                                                      VALUES(sys_guid(),'{0}','{1}','{2}')";
        strSql = string.Format(strSql, parentID, filePath, fileName);
        DatabaseHelper.ExecuteSql(strSql);
    }

    /// <summary>
    /// 获取工作薄对象
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public IWorkbook GetWorkbookObj(string filePath)
    {
        FileStream fileStream = null;
        try
        {
            string IsXls = Path.GetExtension(filePath);
            //读取文件流
            fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            fileStream.Position = 0;
            IWorkbook Workbook = null;
            switch (IsXls)
            {
                case ".xlsx":
                    Workbook = new XSSFWorkbook(fileStream);
                    break;
                case ".xls":
                    Workbook = new HSSFWorkbook(fileStream);
                    break;
                default:
                    break;
            }
            fileStream.Close();//关闭文件流
            return Workbook;
        }
        catch (Exception ex)
        {
            fileStream.Close();//关闭文件流
            return null;
        }
    }

    /// <summary>
    /// 获取分厂信息
    /// </summary>
    /// <param name="factoryName"></param>
    /// <returns></returns>
    private DataTable GetFactoryInfo(string factoryName)
    {
        string sql = string.Format(@"SELECT f.factoryname,f.factoryid,u.attributevalue factorycode FROM factory f 
                        LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
                        WHERE f.factoryname = '{0}'", factoryName);
        return DatabaseHelper.Query_ReturnDatTable(sql);
    }

    /// <summary>
    /// 获取工艺名
    /// </summary>
    /// <param name="factoryCode"></param>
    /// <param name="productName"></param>
    /// <returns></returns>
    private string GetWorkflowName(string factoryCode, string productName)
    {
        string workflowNum = string.Format("WF-{0}GF-{1}-", factoryCode, productName);
        string sql = string.Format(@"SELECT wb.workflowname FROM workflowbase wb WHERE wb.workflowname LIKE '{0}%'", workflowNum);
        DataTable dtWorkflow = DatabaseHelper.Query_ReturnDatTable(sql);
        int maxNo = 0;//最大的编号
        int currNo = 0;//当前的编号
        foreach (DataRow item in dtWorkflow.Rows)
        {
            string[] sp = item["workflowname"].ToString().Replace(workflowNum, "").Split('-');
            if (sp.Length != 2) //如果不是分割成两份，则不累加
            {
                continue;
            }
            if (sp.Last() != "P") //如果最后一个分割字符不是P，则不累加
            {
                continue;
            }
            if (int.TryParse(sp.First(), out currNo))
            {
                if (currNo > maxNo) //如果当前的编号大于最大编号，则赋给最大编号
                {
                    maxNo = currNo;
                }
            }
        }
        maxNo++;//最后自增，得到最新的工艺数据。
        return string.Format("{0}{1}-P", workflowNum, maxNo.ToString().PadLeft(5, '0'));
    }
    /// <summary>
    /// 工序类型（后续新增类型，必须在此添加，统一数据来源）
    /// </summary>
    private enum SpecType
    {
        普通工序,
        转工工序,
        检验工序,
        转工检验工序
    }
    /// <summary>
    /// 获取单元格值
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private string GetCellValue(ICell cell)
    {
        if (cell == null)
        {
            return "";
        }
        try
        {
            if (cell.CellType == CellType.Formula)//判断单元格是否有公式
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    DateTime date = DateTime.FromOADate(cell.NumericCellValue);
                    if (date.Hour == 0)
                    {
                        return date.ToString("yyyy-MM-dd");
                    }
                    return date.ToString("yyyy-MM-dd HH:mm:ss");
                }
                cell.SetCellType(CellType.String);
            }
            if (cell.CellType == CellType.Numeric)
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    DateTime date = DateTime.FromOADate(cell.NumericCellValue);
                    if (date.Hour == 0)
                    {
                        return date.ToString("yyyy-MM-dd");
                    }
                    return date.ToString("yyyy-MM-dd HH:mm:ss");
                }
                cell.SetCellType(CellType.String);
            }
        }
        catch
        {
            cell.SetCellType(CellType.String);
        }
        return cell.ToString().Trim();
    }

    /// <summary>
    /// 验证工序的行数据是否有效
    /// </summary>
    /// <param name="row"></param>
    /// <param name="i"></param>
    /// <param name="drWorkflow"></param>
    /// <returns></returns>
    private Tuple<bool, bool, string> IsValidSpecRow(ref DataRow row, int i, DataRow drWorkflow)
    {
        int c = 0;
        foreach (var item in row.ItemArray)
        {
            if (string.IsNullOrWhiteSpace(item.ToString()))
            {
                c++;
            }
        }
        if (c != row.ItemArray.Length) //如果该工序行有数据，则验证数据是否有效和必填
        {
            /*
             * 【1、验证必填字段】。说明：这些字段为必填
             */
            if (string.IsNullOrWhiteSpace(row["SpecNo"].ToString())) //验证工序号
            {
                return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序号为空，请检查文件数据", i));
            }
            if (string.IsNullOrWhiteSpace(row["OpDesc"].ToString())) //验证工序名称
            {
                return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序名称为空，请检查文件数据", i));
            }
            if (string.IsNullOrWhiteSpace(row["ApsMakeTime"].ToString())) //验证排产加工工时
            {
                return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的排产加工工时为空，请检查文件数据", i));
            }
            /*
             * 【2、验证工序类型】。说明：如果未填写工序类型，则默认为普通工序。如填写后，则验证是否与系统规定的工序类型一致。
             */
            if (string.IsNullOrWhiteSpace(row["SpecType"].ToString())) //如果未填写，则默认为普通工序
            {
                row["SpecType"] = SpecType.普通工序;
            }
            else
            {
                string[] stList = Enum.GetNames(typeof(SpecType));
                if (!stList.Contains(row["SpecType"].ToString())) //系统不包含该工序类型，则验证不通过
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序类型暂不支持，请检查文件数据", i));
                }
            }
            /*
            * 【3、验证工序分厂】。说明：转工工序一定要维护分厂属性且不能与工艺所属分厂相同。如果分厂为空，则默认为工艺所在的分厂
            */
            if ((SpecType)Enum.Parse(typeof(SpecType), row["SpecType"].ToString()) == SpecType.转工工序 || (SpecType)Enum.Parse(typeof(SpecType), row["SpecType"].ToString()) == SpecType.转工检验工序)
            {
                if (string.IsNullOrWhiteSpace(row["SpecFactoryName"].ToString())) //如果未填写分厂，则验证不通过
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序是：{1}，须填写分厂，请检查文件数据", i, row["SpecType"].ToString()));
                }
                if (row["SpecFactoryName"].ToString() == drWorkflow["WorkflowFactoryName"].ToString()) //维护的分厂不能和工艺所属分厂相同
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序是：{1}，所属分厂不应该与工艺所属分厂相同，请检查文件数据", i, row["SpecType"].ToString()));
                }
            }
            else
            {
                if (string.IsNullOrWhiteSpace(row["SpecFactoryName"].ToString())) //如果为空，则默认为工艺所在分厂
                {
                    row["SpecFactoryName"] = drWorkflow["WorkflowFactoryName"].ToString();
                }
                if (row["SpecFactoryName"].ToString() != drWorkflow["WorkflowFactoryName"].ToString()) //如果维护的分厂不和工艺所属分厂相同，则验证不通过（只要不是转工工序，理论上这里工序分厂应该与工艺分厂相同，如有特殊情况，单独做判断）
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序是：{1}，所属分厂应该与工艺所属分厂相同，请检查文件数据", i, row["SpecType"].ToString()));
                }
            }
            DataTable dtFactory = GetFactoryInfo(row["SpecFactoryName"].ToString()); //获取工序的分厂，判断是否在系统中存在
            if (dtFactory.Rows.Count == 0)
            {
                return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序数据有误，分厂{1}不存在，请检查文件数据", i, row["SpecFactoryName"].ToString()));
            }
            /*
             * 【4、验证工序设备】。工艺类型为普通工序，需填写设备信息，其他类型可不填写。如果填写了设备，需验证设备是否有效
             */
            if ((SpecType)Enum.Parse(typeof(SpecType), row["SpecType"].ToString()) == SpecType.普通工序)
            {
                if (string.IsNullOrWhiteSpace(row["ApsResource"].ToString()))
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序需填写设备，请检查文件数据", i));
                }
            }
            if (!string.IsNullOrWhiteSpace(row["ApsResource"].ToString())) //如果设备不为空，则验证设备是否有效
            {
                DataTable dtResource = GetResourceInfo(row["ApsResource"].ToString());
                if (dtResource.Rows.Count == 0) //如果查询不到设备，则验证不通过
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行的工序数据有误，设备：{0}不存在，请检查文件数据", i, row["ApsResource"].ToString()));
                }
                if (dtResource.Rows[0]["factoryname"].ToString() != row["SpecFactoryName"].ToString()) //如果设备的分厂与工序的分厂不一致，则验证不通过
                {
                    return new Tuple<bool, bool, string>(false, false, string.Format("第{0}行工序的设备所属分厂与工序分厂不一致，请检查文件数据", i));
                }
                row["ApsResourceID"] = dtResource.Rows[0]["resourceid"].ToString();//记录下设备ID，保存工序时会添加到工序设备关联表中。
            }
            /*-------------验证完毕END------------------------*/
            return new Tuple<bool, bool, string>(true, true, "验证通过");
        }
        return new Tuple<bool, bool, string>(true, false, "");//表明该行是空行，跳过插入和验证
    }
    /// <summary>
    /// 删除文件
    /// </summary>
    /// <param name="fpath">文件地址</param>
    private void DeleteFile(string fpath)
    {
        if (File.Exists(fpath))
        {
            new FileInfo(fpath).Attributes = FileAttributes.Normal;
            File.Delete(fpath);
        }
    }
    /// <summary>
    /// 没有文件路径，则创建
    /// </summary>
    /// <param name="context"></param>
    private void CreateDirectory(HttpContext context)
    {
        if (!Directory.Exists(context.Server.MapPath(fileBasePath)))
        {
            Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
        }
    }

    /// <summary>
    /// 获取设备信息
    /// </summary>
    /// <param name="resourceName"></param>
    /// <returns></returns>
    private DataTable GetResourceInfo(string resourceName)
    {
        string sql = string.Format(@"SELECT r.resourceid,f.factoryname FROM resourcedef r
                    LEFT JOIN Factory f ON f.factoryid = r.factoryid
                     WHERE r.resourcename = '{0}'", resourceName);
        return DatabaseHelper.Query_ReturnDatTable(sql);
    }

    #endregion

}